package com.agri.mapper;

import com.agri.entity.CropPriceTrend;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;

/**
 * 农产品价格趋势Mapper接口
 * 提供农产品价格趋势数据的CRUD操作
 *
 * @author Agri-Fin-Sale Team
 * @since 2025-10-31
 */
@Mapper
public interface CropPriceTrendMapper extends BaseMapper<CropPriceTrend> {

    /**
     * 根据农产品ID查询价格趋势
     * @param cropId 农产品ID
     * @return 价格趋势列表
     */
    @Select("SELECT * FROM t_crop_price_trend WHERE crop_id = #{cropId} ORDER BY price_date DESC")
    List<CropPriceTrend> selectByCropId(@Param("cropId") Long cropId);

    /**
     * 查询指定日期范围内的价格趋势
     * @param cropId 农产品ID
     * @param startDate 开始日期
     * @param endDate 结束日期
     * @return 价格趋势列表
     */
    @Select("SELECT * FROM t_crop_price_trend " +
            "WHERE crop_id = #{cropId} AND price_date BETWEEN #{startDate} AND #{endDate} " +
            "ORDER BY price_date ASC")
    List<CropPriceTrend> selectByDateRange(@Param("cropId") Long cropId,
                                          @Param("startDate") Date startDate,
                                          @Param("endDate") Date endDate);

    /**
     * 查询最新价格
     * @param cropId 农产品ID
     * @return 最新价格趋势
     */
    @Select("SELECT * FROM t_crop_price_trend " +
            "WHERE crop_id = #{cropId} " +
            "ORDER BY price_date DESC LIMIT 1")
    CropPriceTrend selectLatestPrice(@Param("cropId") Long cropId);

    /**
     * 查询多个农产品的最新价格
     * @param cropIds 农产品ID列表
     * @return 价格趋势列表
     */
    @Select("<script>" +
            "SELECT cpt.* FROM t_crop_price_trend cpt " +
            "INNER JOIN (" +
            "   SELECT crop_id, MAX(price_date) as max_date " +
            "   FROM t_crop_price_trend " +
            "   WHERE crop_id IN " +
            "   <foreach item='id' collection='cropIds' open='(' separator=',' close=')'>" +
            "       #{id}" +
            "   </foreach>" +
            "   GROUP BY crop_id" +
            ") latest ON cpt.crop_id = latest.crop_id AND cpt.price_date = latest.max_date" +
            "</script>")
    List<CropPriceTrend> selectLatestPrices(@Param("cropIds") List<Long> cropIds);
}